using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace FederationMembershipDB
{
	public class Member
	{
		int _MemberID;
        string _Title;
		string _GivenName;
        string _LastName;
        string _Gender;
        DateTime _DOB;
		object _Photo;
		string _HmPhone;
		string _WkPhone;
		string _Mobile;
		string _Email;
		string _Skype;
		string _Address;
		string _MartialArtsHistory;
		string _Comments;
		EmergencyContact _EmergencyContact;
		MedicalHistory _MedicalHistory;
        int _TrainingPassID;

        public int ID
        {
            get { return _MemberID; }
            set { _MemberID = value; }
        }

        public string GivenName
        {
            get { return _GivenName; }
            set { _GivenName = value; }
        }

        public string LastName
        {
            get { return _LastName; }
            set { _LastName = value; }
        }

        public override string ToString()
        {
            return string.Format("{0} - {1}, {2}", _MemberID, _LastName, _GivenName);
        }

        public void AddMember(int memberid, string givenname,  string lastname, string gender, DateTime dob, string photo, string hmphone, string wkphone, string mobile, string email, string skype, string address, string martialartshistory, string comments, EmergencyContact emergencycontact, MedicalHistory medicalhistory)
		{
            _MemberID = memberid;
            _GivenName = givenname;
            _LastName = lastname;
            _Gender = gender;
            _DOB = dob;
            _Photo = photo;
            _HmPhone = hmphone;
            _WkPhone = wkphone;
            _Mobile = mobile;
            _Email = email;
            _Skype = skype;
            _Address = address;
            _MartialArtsHistory = martialartshistory;
            _Comments = comments;
            _EmergencyContact = emergencycontact;
            _MedicalHistory = medicalhistory;

            //Add to database
            Utilities.openDatabase();

            SqlCommand insertMember = new SqlCommand(string.Format("INSERT INTO Member(ID, GivenName, LastName, Gender, DOB, Photo, Hmphone, Wkphone, Mobile, Email, Skype, Address, MartialArtsHistory, Comments, Emergencycontact_ID, MedicalHistory_ID) VALUES({0}, '{1}', '{2}', '{3}', CONVERT(datetime, '{4}', 103), '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', {14}, {15} )",
                                                                               _MemberID, _GivenName, _LastName, _Gender, _DOB, _Photo, _HmPhone, _WkPhone, _Mobile, _Email, _Skype, _Address, _MartialArtsHistory, _Comments, _EmergencyContact.ID, _MedicalHistory.ID), Utilities.connection);
            insertMember.ExecuteNonQuery();
            Utilities.connection.Close();
		}

        public string[] ViewMember(int memberid)
        {
            string[] ret = new string[13];
            _MemberID = memberid;

            Utilities.openDatabase();

            SqlCommand getdata = new SqlCommand(string.Format("SELECT GivenName, LastName, Gender, CONVERT(varchar(10), DOB, 103), Photo, Hmphone, Wkphone, Mobile, Email, Skype, Address, MartialArtsHistory, Comments FROM Member WHERE ID = {0}", _MemberID), Utilities.connection);
            SqlDataReader dr1 = getdata.ExecuteReader();

            if (dr1.HasRows)
            {
                dr1.Read();
                for (int i = 0; i < 13; i++)
                {
                    if (dr1[i] == DBNull.Value)
                        ret[i] = "";
                    else
                        ret[i] = (string)dr1[i];
                }

                Utilities.connection.Close();
                return ret;
            }
            else
            {
                Utilities.connection.Close();
                return null;
            }


        }

        public void EditMember(int memberid, string givenname, string lastname, string gender, DateTime dob, string photo, string hmphone, string wkphone, string mobile, string email, string skype, string address, string martialartshistory, string comments)
        {
            _MemberID = memberid;
            _GivenName = givenname;
            _LastName = lastname;
            _Gender = gender;
            _DOB = dob;
            _Photo = photo;
            _HmPhone = hmphone;
            _WkPhone = wkphone;
            _Mobile = mobile;
            _Email = email;
            _Skype = skype;
            _Address = address;
            _MartialArtsHistory = martialartshistory;
            _Comments = comments;

            //Add to database
            Utilities.openDatabase();

            SqlCommand editmember = new SqlCommand(string.Format("UPDATE Member SET GivenName = '{0}', LastName = '{1}', Gender = '{2}', DOB = CONVERT(datetime, '{3}', 103), Hmphone = '{4}', Wkphone = '{5}', Mobile = '{6}', Email = '{7}', Skype = '{8}', Address = '{9}', MartialArtsHistory = '{10}' , Comments = '{11}', Photo = '{12}' WHERE ID = {13}",
                                                                               _GivenName, _LastName, _Gender, _DOB, _HmPhone, _WkPhone, _Mobile, _Email, _Skype, _Address, _MartialArtsHistory, _Comments, _Photo, _MemberID), Utilities.connection);
            editmember.ExecuteNonQuery();
            Utilities.connection.Close();
        }

        public void InsertTrainingPassID(int intTrainingPassID)
        {
            //Update database with TrainingPassID
            Utilities.openDatabase();
            SqlCommand editmember = new SqlCommand(string.Format("UPDATE Member SET TrainingPass_ID = " + intTrainingPassID + " WHERE ID = " +
                _MemberID + ";"), Utilities.connection);
            editmember.ExecuteNonQuery();
            Utilities.connection.Close();
        }
	}
}
